package com.yueke.gemini.jdbc;


import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * jdbc 增删查改封装.Java
 */
public class DbUtil_IDUS {

    //分页属性
    private int currpage = 1;// 当前页
    private int pagesize = 5;// 每页条数
    private int recordcount = 0;// 总条数
    private int pagecount = 0;// 总页数

    private Connection conn = null;//链接数据库对象
    private String dbname = "users";//数据库名
    private int port = 3306;//端口号
    private String username = "root";//数据库名
    private String pwd = "root";//数据库名


    /**
     * 链接数据库
     * @param database
     * @param port
     * @param name
     * @param pwd
     * @return
     */
    public Connection connection(String database, int port, String name, String pwd) {
        Connection conn=null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //建立连接
            String url="jdbc:mysql://localhost:"+port+"/"+database+"?useUnicode=true&characterEncoding=utf8";
            conn= DriverManager.getConnection(url, name, pwd);
            //4判断是否连接成功
            if(!conn.isClosed()){
                System.out.println("数据库连接成功");
            }else {
                System.out.println("数据库连接失败");
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获取表中主键
     * @param tablename
     * @return
     */
    public String getPk(String tablename) {
        String pk = null;
        DatabaseMetaData dbmd;
        try {
            dbmd = this.conn.getMetaData();
            ResultSet rs = dbmd.getPrimaryKeys(this.dbname, null, tablename);
            if (rs.next()) {
                pk = rs.getString(4);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pk;
    }

    //添加数据
    /**
     * 以数组形式添加  1、sql语句   2.数组
     * @param sql
     * @param values
     * @return
     */
    public int add(String sql, Object[] values) {
        int num = 0;
        PreparedStatement pst;
        try {
            pst = this.conn.prepareStatement(sql);
            int i = 0;
            for (Object o : values) {
                pst.setObject(++i, o);
            }
            num = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    /**
     * 以map添加数据 1.表名  2.map
     * @param tablename
     * @param m
     * @return
     */
    public int insert(String tablename, Map<String, Object> m) {
        int num = 0;
        StringBuilder n = new StringBuilder();
        StringBuilder v = new StringBuilder();
        for (String k : m.keySet()) {
            v.append("?,");
            n.append(k + ",");
        }
        String sql = String.format("insert into %s(%s) values(%s)", tablename, n.toString().subSequence(0, n.length() - 1), v.toString().subSequence(0, v.length() - 1));
        PreparedStatement pst;
        try {
            pst = this.conn.prepareStatement(sql);
            int i = 0;
            for (Object o : m.values()) {
                pst.setObject(++i, o);
            }
            num = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    //修改数据
    /**
     * 以主键修改需要的字段
     * @param tablename
     * @param m
     * @return
     */
    public int update(String tablename, Map<String, Object> m) {
        int num = 0;
        String pk = this.getPk(tablename);
        if (m.containsKey(pk)) {
            num = update(tablename, m, pk + "=" + m.get(pk));
        } else {
            num = update(tablename, m, "1=1");
        }
        return num;
    }

    /**
     * 按条件修改需要的字段
     * @param tablename
     * @param m
     * @param where
     * @return
     */
    public int update(String tablename, Map<String, Object> m, String where) {
        int num = 0;
        StringBuilder s = new StringBuilder();
        for (String k : m.keySet()) {
            s.append(k + "=?,");
        }
        String sql = String.format("update %s set %s where %s", tablename, s.toString().subSequence(0, s.length() - 1), where);
        PreparedStatement pst;
        try {
            pst = this.conn.prepareStatement(sql);
            int i = 0;
            for (Object o : m.values()) {
                pst.setObject(++i, o);
            }
            num = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }


    //删除数据
    /**
     * 删除表
     * @param tablename
     * @return
     */
    public int delete(String tablename) {
        int num = delete(tablename, "1=1");
        return num;
    }

    /**
     * 以id删除数据
     * @param tablename
     * @param id
     * @return
     */
    public int deleteById(String tablename, Object id) {
        int num = delete(tablename, this.getPk(tablename) + "=" + id);
        return num;
    }

    /**
     * 以 where 条件删除数据
     * @param tablename
     * @param where
     * @return
     */
    public int delete(String tablename, String where) {
        int num = 0;
        String sql = String.format("delete from %s where %s", tablename, where);
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            num = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }


    // 查询数据
    /**
     *  以id查询某条数据
     * @param tablename
     * @param id
     * @return
     */
    public Map<String, Object> queryById(String tablename, Object id) {
        Map<String, Object> m = new HashMap<String, Object>();
        String sql = String.format("select * from %s where %s", tablename, this.getPk(tablename) + "=" + id);
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int cc = rsmd.getColumnCount();
                for (int i = 1; i <= cc; i++) {
                    String name = rsmd.getColumnLabel(i);
                    m.put(name, rs.getObject(name));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return m;
    }
    //分页逻辑
    /**
     * 分页：1.当前页   2.表名
     * @param currpage
     * @param tablename
     * @return
     */
    public List<Map<String, Object>> page(int currpage, String tablename) {
        return page(currpage,tablename,"*","where 1=1","");
    }

    /**
     * 分页：1.当前页   2.表名   3.排序
     * @param currpage
     * @param tablename
     * @param order
     * @return
     */
    public List<Map<String, Object>> page(int currpage, String tablename, String order) {
        return page(currpage,tablename,"*","where 1=1",order);
    }


    /**
     * 分页：1.当前页   2.表名   3.条件   4.排序
     * @param currpage
     * @param tablename
     * @param where
     * @param order
     * @return
     */
    public List<Map<String, Object>> page(int currpage, String tablename, String where, String order) {
        return page(currpage,tablename,"*",where,order);
    }


    /**
     * 分页：1.当前页   2.表名   3.查询字段   4.条件   5.排序
     * @param currpage
     * @param tablename
     * @param fields
     * @param where
     * @param order
     * @return
     */
    public List<Map<String, Object>> page(int currpage, String tablename, String fields, String where, String order) {
        //当前页
        this.currpage = currpage;
        //实例化map集合
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        //查询sql
        String sql = String.format("select %s from %s %s %s limit ?,?", fields, tablename, where, order);
        //查询总条数
        String qqq = String.format("select count(*) c from %s %s", tablename, where);
        try {
            // 分页信息
            PreparedStatement qpst = this.conn.prepareStatement(qqq);
            ResultSet qrs = qpst.executeQuery();
            if (qrs.next()) {
                //总条数
                this.recordcount = qrs.getInt("c");
                //总页数
                this.pagecount = this.recordcount % this.pagesize == 0 ? this.recordcount / this.pagesize : this.recordcount / this.pagesize + 1;
            }
            //当前页
            if(this.currpage < 1) this.currpage = 1;
            if(this.currpage > this.pagecount) this.currpage = this.pagecount;

            // 分页结果信息
            PreparedStatement pst = this.conn.prepareStatement(sql);
            pst.setInt(1, this.currpage * this.pagesize - this.pagesize);
            pst.setInt(2, this.pagesize);
            ResultSet rs = pst.executeQuery();
            //获取数据表信息
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> m = new HashMap<String, Object>();
                //获取数据表列数
                int cc = rsmd.getColumnCount();
                for (int i = 1; i <= cc; i++) {
                    //获取列的别名
                    String name = rsmd.getColumnLabel(i);
                    //String name = rsmd.getColumnName(i); //获取列名
                    //System.out.println(name+"***");
                    m.put(name, rs.getObject(name));
                }
                list.add(m);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * bootstrap 分页样式
     * @return
     */
    public String pagebootstrap() {
        StringBuilder s = new StringBuilder();
        s.append("<ul class=\"pagination\">");
        int start = 1;
        int end = 10;

        if (this.currpage >= 7) {
            start = this.currpage - 5;
            end = this.currpage + 4;
        }
        if (this.currpage != 1) {
            s.append(String.format("<li><a class=\"prev\" href=\"?p=%d\">上一页</a></li>", this.currpage - 1));
        }
        for (int i = start; i <= end; i++) {
            if (i > this.pagecount)
                break;
            if (this.currpage == i) {
                s.append(String.format("<li class=\"active\"><a href=\"javascript:void(0)\">%d</a></li>", i));
                continue;
            }
            s.append(String.format("<li><a href=\"?p=%d\">%d</a></li>", i, i));
        }
        if (this.currpage < this.pagecount) {
            s.append(String.format("<li><a class=\"next\" href=\"?p=%d\">下一页</a></li>", this.currpage + 1));
        }
        s.append("</ul>");
        return s.toString();
    }
    /**
     * 分页样式
     * @return
     */
    public String pageinfo(){
        StringBuilder s = new StringBuilder();
        s.append("<div class=\"page\">");
        int start = 1;
        int end = 10;

        if(this.currpage>=7){
            start = this.currpage-5;
            end = this.currpage+4;
        }
        if(this.currpage!=1){
            s.append(String.format("<a class=\"prev\" href=\"?p=%d\">上一页</a>",this.currpage-1));
        }
        for(int i=start;i<=end;i++){
            if(i>this.pagecount) break;
            if(this.currpage == i){
                s.append(String.format("<span >%d</span>",i));
                continue;
            }
            s.append(String.format("<a href=\"?p=%d\">%d</a>",i,i));
        }
        if(this.currpage<this.pagecount){
            s.append(String.format("<a class=\"next\" href=\"?p=%d\">下一页</a>",this.currpage+1));
        }
        s.append("</div>");
        return s.toString();
    }

    //get,set方法
    public int getCurrpage() {
        return currpage;
    }
    public void setCurrpage(int currpage) {
        this.currpage = currpage;
    }
    public int getPagesize() {
        return pagesize;
    }
    public void setPagesize(int pagesize) {
        this.pagesize = pagesize;
    }
    public int getRecordcount() {
        return recordcount;
    }
    public void setRecordcount(int recordcount) {
        this.recordcount = recordcount;
    }
    public int getPagecount() {
        return pagecount;
    }
    public void setPagecount(int pagecount) {
        this.pagecount = pagecount;
    }

    //默认数据库，端口号，用户名，密码
    public DbUtil_IDUS() {
        this.conn = connection(this.dbname, this.port, this.username, this.pwd);
    }

    //自定义数据库，端口号，用户名，密码
    public DbUtil_IDUS(String dbname, int port, String username, String pwd) {
        this.dbname = dbname;
        this.conn = connection(dbname, port, username, pwd);
    }


}

